﻿using FrameworkCore.Constants;
using FrameworkCore.Data;
using FrameworkCore.Migrations.Data;
using Microsoft.Data.SqlClient;
using Microsoft.EntityFrameworkCore.Query;
using Microsoft.Extensions.Configuration;
using System.Linq.Expressions;
using System.Text.RegularExpressions;

namespace FrameworkCore.Migrations;

public abstract partial class BaseDataProvider
{
    #region 构造函数
    protected BaseDataProvider()
    {
    }
    #endregion

    #region 创建数据库
    private string databaseNamePattern = @"\s*;\s*Database\s*=\s*([^;]+)\s*;\s*";
    private string connectionStringPattern = @"(?i)Database=";
    public virtual string DatabaseName => CommonHelper.MatchString(ConnectionString, databaseNamePattern).Trim();

    public virtual string ConnectionString => ConfigurationsExtensions.GetGlobalSection<string>(GlobalConstants.DefaultConnectionKey);

    public virtual bool CreateDatabaseIfNotExists(string? connectionString = default)
    {
        var databaseName = connectionString == default ? DatabaseName : CommonHelper.MatchString(connectionString, databaseNamePattern);
        connectionString = connectionString ?? ConnectionString;
        connectionString = Regex.Replace(connectionString, connectionStringPattern, "").Replace(databaseName, "");

        // 检查数据库是否已经存在
        if (DatabaseExists(databaseName, connectionString))
        {
            Console.WriteLine($"Database '{databaseName}' already exists.");
            return false;
        }

        // 创建数据库的 SQL 查询字符串
        string createDatabaseQuery = $"CREATE DATABASE [{databaseName}]";

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            try
            {
                connection.Open();
                using (SqlCommand command = new SqlCommand(createDatabaseQuery, connection))
                {
                    command.ExecuteNonQuery();
                    Console.WriteLine($"Database '{databaseName}' created successfully.");
                    return true;
                }
            }
            catch (SqlException ex)
            {
                Console.WriteLine($"Error creating database '{databaseName}': {ex.Message}");
                return false;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error creating database '{databaseName}': {ex.Message}");
                return false;
            }
        }
    }

    public virtual bool DatabaseExists(string? databaseName = default, string? connectionString = default)
    {
        connectionString = connectionString ?? ConnectionString;
        databaseName = databaseName ?? DatabaseName;
        string query = "SELECT COUNT(*) FROM sys.databases WHERE name = @databaseName";
        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            using (SqlCommand command = new SqlCommand(query, connection))
            {
                command.Parameters.AddWithValue("@databaseName", databaseName);
                connection.Open();
                int result = (int)command.ExecuteScalar();
                return result > 0;
            }
        }
    }
    #endregion

    #region 增删改
    /// <summary>
    /// 将记录插入表中。返回带有自增主键的插入实体。
    /// </summary>
    /// <typeparam name="TEntity">要插入的实体</typeparam>
    /// <param name="entity">实体类型</param>
    /// <param name="cancellationToken">取消令牌</param>
    /// <returns>
    /// 表示异步操作的任务
    /// 任务结果包含插入的实体
    /// </returns>
    public abstract  Task<TEntity> InsertEntityAsync<TEntity>(
        TEntity entity,
        CancellationToken cancellationToken = default)
        where TEntity : BaseEntity;

    /// <summary>
    /// 将记录插入表中。返回带有自增主键的插入实体。
    /// </summary>
    /// <param name="entity">要插入的实体</param>
    /// <typeparam name="TEntity">实体类型</typeparam>
    /// <returns>插入的实体</returns>
    public abstract TEntity InsertEntity<TEntity>(TEntity entity)
        where TEntity : BaseEntity;

    /// <summary>
    /// 使用实体参数的值更新表中的记录。
    /// 根据主键值匹配实体确定要更新的记录。
    /// </summary>
    /// <param name="entity">带有更新数据的实体</param>
    /// <typeparam name="TEntity">实体类型</typeparam>
    public abstract void UpdateEntity<TEntity>(TEntity entity) 
        where TEntity : BaseEntity;

    /// <summary>
    /// 使用实体参数的值更新表中的记录。
    /// 根据主键值匹配实体确定要更新的记录。
    /// </summary>
    /// <param name="entities">带有更新数据的实体集合</param>
    /// <typeparam name="TEntity">实体类型</typeparam>
    public abstract void UpdateEntities<TEntity>(IEnumerable<TEntity> entities)
        where TEntity : BaseEntity;

    /// <summary>
    /// 根据条件在表中执行批量修改记录操作。
    /// </summary>
    /// <typeparam name="TEntity">实体类型</typeparam>
    /// <param name="setPropertyCalls">修改的列</param>
    /// <param name="predicate">用于修改每个元素条件的函数</param>
    /// <param name="cancellationToken">取消令牌</param>
    /// 表示异步操作的任务
    /// 任务结果包含修改的记录数
    /// <returns></returns>
    public abstract Task<int> BulkUpdateEntitiesAsync<TEntity>(
        Expression<Func<SetPropertyCalls<TEntity>, SetPropertyCalls<TEntity>>> setPropertyCalls, 
        Expression<Func<TEntity, bool>> predicate, 
        CancellationToken cancellationToken = default)
        where TEntity : BaseEntity;

    /// <summary>
    /// 根据条件在表中执行批量修改记录操作。
    /// </summary>
    /// <typeparam name="TEntity">实体类型</typeparam>
    /// <param name="setPropertyCalls">修改的列</param>
    /// <param name="predicate">用于修改每个元素条件的函数</param>
    /// 修改的记录数
    /// <returns></returns>
    public abstract int BulkUpdateEntities<TEntity>(
        Expression<Func<SetPropertyCalls<TEntity>, SetPropertyCalls<TEntity>>> setPropertyCalls, 
        Expression<Func<TEntity, bool>> predicate)
        where TEntity : BaseEntity;

    /// <summary>
    /// 删除表中的记录。要删除的记录通过与实体对象的主键值匹配来确定。
    /// </summary>
    /// <param name="entity">删除操作的实体</param>
    /// <typeparam name="TEntity">实体类型</typeparam>
    public abstract void DeleteEntity<TEntity>(TEntity entity)
        where TEntity : BaseEntity;

    /// <summary>
    /// 在表中执行批量删除记录操作。
    /// </summary>
    /// <param name="entities">要删除的实体集合</param>
    /// <typeparam name="TEntity">实体类型</typeparam>
    public abstract void BulkDeleteEntities<TEntity>(IList<TEntity> entities) 
        where TEntity : BaseEntity;

    /// <summary>
    /// 根据条件在表中执行批量删除记录操作。
    /// </summary>
    /// <param name="predicate">用于测试每个元素条件的函数</param>
    /// <typeparam name="TEntity">实体类型</typeparam>
    /// <returns>
    /// 表示异步操作的任务
    /// 任务结果包含删除的记录数
    /// </returns>
    public abstract Task<int> BulkDeleteEntitiesAsync<TEntity>(Expression<Func<TEntity, bool>> predicate, CancellationToken cancellationToken = default) 
        where TEntity : BaseEntity;

    /// <summary>
    /// 根据条件在表中执行批量删除记录操作。
    /// </summary>
    /// <param name="predicate">用于测试每个元素条件的函数</param>
    /// <typeparam name="TEntity">实体类型</typeparam>
    /// <returns>
    /// 删除的记录数
    /// </returns>
    public abstract int BulkDeleteEntities<TEntity>(
        Expression<Func<TEntity, bool>> predicate) 
        where TEntity : BaseEntity;


    /// <summary>
    /// 对实体集合执行批量插入操作。
    /// </summary>
    /// <param name="entities">要插入的实体集合</param>
    /// <param name="cancellationToken">取消令牌</param>
    /// <typeparam name="TEntity">实体类型</typeparam>
    /// <returns>表示异步操作的任务</returns>
    public abstract Task BulkInsertEntitiesAsync<TEntity>(IEnumerable<TEntity> entities,
        CancellationToken cancellationToken = default)
        where TEntity : BaseEntity;

    /// <summary>
    /// 对实体集合执行批量插入操作。
    /// </summary>
    /// <param name="entities">要插入的实体集合</param>
    /// <typeparam name="TEntity">实体类型</typeparam>
    public abstract void BulkInsertEntities<TEntity>(IEnumerable<TEntity> entities)
        where TEntity : BaseEntity;

    /// <summary>
    /// 保存
    /// </summary>
    /// <param name="cancellationToken">取消令牌</param>
    /// <returns></returns>
    public abstract Task<int> SaveChangesAsync(CancellationToken cancellationToken = default);

    /// <summary>
    /// 保存
    /// </summary>
    /// <returns></returns>
    public abstract int SaveChanges();

    #endregion

    #region 查询
    public abstract IQueryable<TEntity> Query<TEntity>() where TEntity : BaseEntity;
    #endregion
}
